﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using KCL.Code.Entities;

namespace KCL.Code.Data
{
    public class MaterialDAO : BaseDAO
    {
        public List<MaterialView> GetAllMaterialViews()
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var lstMaterialViews = ctx.ExecuteQuery<MaterialView>(@"
                    SELECT Material.code, Material.name, Product.name product, Color.name color,
                            Size.length + ' x ' + Size.width size,
                            Thickness.thick thickness, MadeIn.name madein,
                            Material.quantity
                    FROM		((((Material
                    INNER JOIN  Product
		                    ON	Material.productId = Product.id)
                    INNER JOIN	Color
                            ON	Material.colorId = Color.id)
                    INNER JOIN	Size
                            ON	Material.sizeId = Size.id)
                    INNER JOIN	Thickness
                            ON	Material.thicknessId = Thickness.id)
                    INNER JOIN	MadeIn
                            ON	Material.madeInId = MadeIn.id
                    WHERE   Material.isActive = 1
                    ORDER BY    Material.code, Material.name");
                return lstMaterialViews.AsEnumerable().ToList<MaterialView>();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<MaterialView> GetMaterialsToReport()
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var lstMaterialViews = ctx.ExecuteQuery<MaterialView>(@"
                    SELECT Material.code, Material.name, Product.name product, Color.name color,
                            Size.length + ' x ' + Size.width size,
                            Thickness.thick thickness, MadeIn.name madein,
                            Material.quantity
                    FROM		((((Material
                    INNER JOIN  Product
		                    ON	Material.productId = Product.id)
                    INNER JOIN	Color
                            ON	Material.colorId = Color.id)
                    INNER JOIN	Size
                            ON	Material.sizeId = Size.id)
                    INNER JOIN	Thickness
                            ON	Material.thicknessId = Thickness.id)
                    INNER JOIN	MadeIn
                            ON	Material.madeInId = MadeIn.id
                    WHERE   Material.isActive = 1 AND Material.quantity > 0
                    ORDER BY    Material.code, Material.name");
                return lstMaterialViews.AsEnumerable().ToList<MaterialView>();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<Material> GetAllMaterials()
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var lstMaterials = from m in ctx.Materials
                                   where m.isActive == true
                                   orderby m.code
                                   select m;
                return lstMaterials.AsEnumerable().ToList();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public Material GetMaterialByID(string code)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var material = (from m in ctx.Materials
                                where m.code.ToUpper() == code.ToUpper()
                                select m).FirstOrDefault();
                return material;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public bool UpdateMaterialQuantity(string code)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();

                var materialToUpdate = (from m in ctx.Materials
                                        where m.code == code
                                        select m).FirstOrDefault();

                var quantityImport = (from mimp in ctx.MaterialImports
                                      where mimp.materialCode == code
                                      select (int?)mimp.quantity).Sum();

                var quantityExport = (from mexp in ctx.MaterialExports
                                      where mexp.materialCode == code
                                      select (int?)mexp.quantity).Sum();

                int quantity = quantityImport == null ? 0 : (int)quantityImport;
                quantity -= (quantityExport == null ? 0 : (int)quantityExport);

                materialToUpdate.quantity = quantity;

                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Insert(Material m)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                ctx.Materials.InsertOnSubmit(m);
                ctx.SubmitChanges();
                return true;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (ex.Number == 2627)
                {
                    try
                    {
                        KCLDataContext ctx = new KCLDataContext();
                        var materialToRestore = (from ma in ctx.Materials
                                                 where ma.code.ToUpper() == m.code.ToUpper()
                                                 && ma.isActive == false
                                                 select ma).FirstOrDefault();
                        materialToRestore.code = m.code;
                        materialToRestore.name = m.name;
                        materialToRestore.productId = m.productId;
                        materialToRestore.colorId = m.colorId;
                        materialToRestore.sizeId = m.sizeId;
                        materialToRestore.thicknessId = m.thicknessId;
                        materialToRestore.madeInId = m.madeInId;
                        materialToRestore.quantity = m.quantity;
                        materialToRestore.isActive = true;
                        ctx.SubmitChanges();
                        return true;
                    }
                    catch (Exception)
                    {
                        return false;
                    }
                }
                return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Update(Material m)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var materialToUpdate = (from ma in ctx.Materials
                                        where ma.code.ToUpper() == m.code.ToUpper()
                                        select ma).FirstOrDefault();
                materialToUpdate.code = m.code.Trim();
                materialToUpdate.name = m.name.Trim();
                materialToUpdate.productId = m.productId;
                materialToUpdate.colorId = m.colorId;
                materialToUpdate.sizeId = m.sizeId;
                materialToUpdate.thicknessId = m.thicknessId;
                materialToUpdate.madeInId = m.madeInId;
                materialToUpdate.quantity = m.quantity;
                materialToUpdate.isActive = m.isActive;
                if (materialToUpdate.code.Length == 0)
                    return false;
                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Delete(Material m)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var materialToDelete = (from ma in ctx.Materials
                                        where ma.code == m.code
                                        select ma).FirstOrDefault();
                materialToDelete.isActive = false;
                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public string GetNextMaterialCode()
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                return ctx.GetNextMaterialCode();
            }
            catch
            {
                return string.Empty;
            }
        }
    }
}